Final exam part 2: How should I pay off my car loan?
by
Molade Osibodu
Objective: To use Excel spreadsheets in determining the best method of paying off your car loan.
Suppose you buy a brand new 2010 Toyota Camry for $100,000. The interest rate charged annually is 12% and you have 3 years to pay off the loan.
Monthly
To determine the monthly payment, we would utilize the payment (PMT) function in excel. To do this, click on a column and type the equation as shown below.
The parameters are rate, period and principal. Note that the rate is divided by month because we want to calculate the monthly value and the period is also converted to months.
For this problem, the monthly payment is $3,321.43. The value is red and in parentheses but use it as a positive number. To avoid getting a negative value, insert a negative (-) sign in front of the principal. This is a present value and thus is reducing each period.
To show the annortization table, label the columns as follows: period, interest charged, fixed payment, payment towards principal and outstanding balance. There would be 36 periods since we have 3 years to pay off the loan. The first column in the outstanding balance will be the principal which is $100,000. The interest charged will be 0.012/12 * the previous balance as shown below:
The fixed payment is the value we calculated as seen above. The payment toward principal is the difference between the fixed payment and the interest charged as shown below:
and the outstanding balance is the difference between the payment toward principal and the previous balance as shown below:
The annortization table is shown below:
Note that the final balance must equal $0 is the fixed monthly payment is calculated correctly. The total interest paid was $19,571.52
What happens if the fixed payment is changed by $1?
We see that the interest increases and we have a balance of $61.64. Thus it is important to either pay the fixed payment or pay more than the fixed payment to pay off the loan in 3 years.
Yearly
Repeating the process as in the monthly case, we have the table below
The yearly payment is $41,634.90 and the total interest paid is $24,904.69
I did the same for quarterly and yearly and the summary is shown below:
We see that the least amount of interest is charged when paying weekly and the greatest when paying yearly. This is realistic because if you deprive the loan company payment money for a year, it makes sense for you to pay more interest.
Also note that the difference between weekly and monthly interest is not great even though the fixed payment is a big difference. Therefore, paying off the loan weekly is the best option.
Click here for excel file
What happens if you choose to pay off the loan daily? What is the fixed payment and total interest charged?